<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use fast\Http;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Cache;
use think\Db;
use think\exception\DbException;
use think\exception\PDOException;
use think\exception\ValidateException;
use think\Log;
use think\process\pipes\Windows;
use think\Request;
use think\Response;
use think\response\Json;

/**
 * 订单处理器
 *
 * @icon fa fa-circle-o
 */
class Profit extends Backend
{

    /**
     * Profit模型对象
     * @var \app\admin\model\Profit
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\Profit();
        $shopsModel = new \app\admin\model\Shops();
        $this->view->assign("shopNoList", $shopsModel->shopsByIdIndexList());
    }

    /**
     * 查看
     *
     * @return string|Json
     * @throws \think\Exception
     * @throws DbException
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        $result = ['total' => 0, 'rows' => []];
        if (false === $this->request->isAjax()) {

            //return         header('Location: http://localsheets.com/67dd4e3aaa734.xlsx',false);

            return $this->view->fetch();
        }

        return json($result);
    }

    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */

    /**
     * 添加
     *
     * @return string
     * @throws \think\Exception
     */
    public function add()
    {
        if (false === $this->request->isPost()) {
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }

        $shopId = $params['shop'] ?? null;
        if (empty($shopId)) {
            $this->error('请选择店铺');
        }

        $shop = \app\admin\model\Shops::get($shopId, ['country']);

        $exchangeRate = $shop['country']['exchange_rate'];

        ini_set("memory_limit", "-1");

        //结算单
        $orderSettlementSheetsPath = ROOT_PATH . 'public/' . $params['settlement'];

        $spreadsheet = IOFactory::load($orderSettlementSheetsPath);
        //$spreadsheet->getSheet()
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 总行数
        //$highestColumn = $worksheet->getHighestColumn(); // 总列数
        //$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5
        $data = [];

        for ($row = 3; $row <= $highestRow; ++$row) {

            $type = $worksheet->getCell('D' . $row)->getValue(); //订单类型
            if ($type != 'Order') {
                continue;
            }
            $orderNo = $worksheet->getCell('E' . $row)->getValue(); //订单ID
            //检查订单是否已经存在结算
            $orderExist = \app\admin\model\Orderprofit::where('order_no', '=',$orderNo)->find();
            if (!empty($orderExist)) {
                continue;
            }
            $item['order_no'] = $orderNo;
            $tk_sku_id = $worksheet->getCell('F' . $row)->getValue(); //TK平台SKUID

            $item['tk_commission_fee'] = $worksheet->getCell('T' . $row)->getValue(); //TK实际佣金
            $item['tk_trade_fee'] = $worksheet->getCell('S' . $row)->getValue(); //交易手续费
            $item['export_transport_fee'] = $worksheet->getCell('U' . $row)->getValue(); //实际物流费
            $item['sfp_fee'] = $worksheet->getCell('AL' . $row)->getValue(); //实际SFP
            $item['super_coupon_fee'] = 0; //实际超级优惠券 todo
            $item['bonus_cash_fee'] = $worksheet->getCell('AN' . $row)->getValue(); //实际奖金返现服务费
            $item['triffile_fee'] = 0; //关税 todo
            $item['added_value_fee'] = $worksheet->getCell('AM' . $row)->getValue(); //增值税VAT
            $item['affiliate_commission_fee'] = $worksheet->getCell('AB' . $row)->getValue(); //达人佣金
            $item['tk_huodong_fee'] = $worksheet->getCell('AP' . $row)->getValue(); //平台活动扣点
            $item['back_money'] = $worksheet->getCell('J' . $row)->getValue(); //实际回款
            $goodsSkuCache = Cache::get($tk_sku_id);
            if (empty($goodsSkuCache)) {
                //获取产品的成本,通过订单号查询订单，获取商品的SKU成本
                $order = \app\admin\model\Order::where('order_no', '=', $orderNo)->find();
                if (empty($order)) {
                    $this->error($item['order_no'] . '订单未导入');
                }
                //Log::error('order::'.json_encode($order));
                $goodsSku = \app\admin\model\Goodssku::where('id', $order['goodssku_id'])->find();
                if (empty($goodsSku)) {

                }
                Log::error(json_encode($goodsSku));
                Cache::set($tk_sku_id, json_encode($goodsSku), 86400);
            } else {
                $goodsSku = json_decode($goodsSkuCache);
            }

            //Cache::get()
            $item['real_profit'] = $item['back_money'] - 1; //实际利润 todo

            //$warehouseName = strstr($warehouseNameO, 'Chinese') ? 'china' : 'local';
            $data[] = $item;
        }

        $itemCnt = count($data);
        $forCnt = floor($itemCnt / 100);

        Log::error(json_encode($data));
        //\app\admin\model\Orderprofit::saveAll($data);

        $this->success('ok');
    }


    /**
     * 添加
     *
     * @return string
     * @throws \think\Exception
     */
    public function addInit()
    {
        if (false === $this->request->isPost()) {
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }

        $region = $params['region'];
        //汇率 todo
        $exchangeRateVn = '3714';
        $shopNo = $params['shop_no'];

        $shopFlag = $shopNo . $region;//A越南

        ini_set("memory_limit", "-1");

        //订单元数据
        $orderSheetsPath = ROOT_PATH . 'public/' . $params['avatar'];
        //结算单
        $orderSettlementSheetsPath = ROOT_PATH . 'public/' . $params['settlement'];

        $spreadsheet = IOFactory::load($orderSheetsPath);
        //$spreadsheet->getSheet()
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 总行数
        $highestColumn = $worksheet->getHighestColumn(); // 总列数
        $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5
        $datas = [];
        $allSkuId = [];
        for ($row = 3; $row <= $highestRow; ++$row) {
            $skuId = $worksheet->getCell('G' . $row)->getValue(); //SKUID
            if (empty($skuId[0]) || $skuId[0] != 'P') {
                continue;
            }
            $orderId = $worksheet->getCell('A' . $row)->getValue(); //订单ID
            $quantity = $worksheet->getCell('J' . $row)->getValue(); //数量
            $orderStatus = $worksheet->getCell('B' . $row)->getValue(); //订单状态
            $sellPrice = $worksheet->getCell('P' . $row)->getValue(); //商品卖价
            $orderAmount = $worksheet->getCell('W' . $row)->getValue(); //订单总额
            $createAt = $worksheet->getCell('Y' . $row)->getValue(); //创建时间
            $paidAt = $worksheet->getCell('Z' . $row)->getValue(); //支付时间
            $cancelledAt = $worksheet->getCell('AD' . $row)->getValue(); //取消时间
            $cancelReason = $worksheet->getCell('AF' . $row)->getValue(); //取消理由
            $warehouseNameO = $worksheet->getCell('AH' . $row)->getValue(); //仓库发货地址
            $warehouseName = strstr($warehouseNameO, 'Chinese') ? 'china' : 'local';

            $deliveryType = $worksheet->getCell('AJ' . $row)->getValue(); //发货类型
            if (!in_array($skuId, $allSkuId)) {
                $allSkuId[] = $skuId;
            }

            $datas[] = ['orderId' => strval($orderId),
                'orderStatus' => $orderStatus,
                'skuId' => $skuId,
                'quantity' => $quantity,
                'sellPrice' => $region == 1 ? intval(str_replace('.', '', $sellPrice)) : $sellPrice,//越南盾取整
                'orderAmount' => $region == 1 ? intval(str_replace('.', '', $orderAmount)) : $orderAmount,//越南盾取整
                'createAt' => $createAt,
                'paidAt' => $paidAt,
                'cancelledAt' => $cancelledAt,
                'cancelReason' => $cancelReason,
                'warehouseName' => $warehouseName,
                'deliveryType' => $deliveryType,
                'totalSettlementAmount' => '0',
                'sellerShippingFee' => '0',
                'transactionFee' => '0',
                'tkCommissionFee' => '0',
                'SPFServiceFee' => '0',
                'SSTFee' => '0',
                'BonusCashbackServiceFee' => '0',
                'VoucherXtraServiceFee' => '0',
                'AffiliateCommission' => '0',
            ];
        }
        Log::error(json_encode($allSkuId));
        //查找所有商品
        $allGoodsSku = (new \app\admin\model\Goodssku)->where('goods_sku', 'in', $allSkuId)->where('country_id', '=', $region)->select();
        if (empty($allGoodsSku)) {
            $this->error('商品未入库');
        }

        $goodsSkuIndexBySkuNo = array_column((array)$allGoodsSku, null, 'goods_sku');
        Log::error(json_encode($goodsSkuIndexBySkuNo));

        $orderIndexByOrderId = array_column($datas, null, 'orderId');
        //var_dump($orderIndexByOrderId);

        //结算单元数据
        $settlementSheet = IOFactory::load($orderSettlementSheetsPath);
        $worksheetSettlement = $settlementSheet->getActiveSheet();
        $highestRowSettlement = $worksheetSettlement->getHighestRow(); // 总行数
        //$highestColumnSettlement = $worksheet->getHighestColumn(); // 总列数
        //var_dump($highestRowSettlement);die();
        for ($i = 2; $i <= $highestRowSettlement; ++$i) {
            $orderIdS = strval($worksheetSettlement->getCell('E' . $i)->getValue()); //订单ID

            /*            if ($region == 1) {
                            $orderIdS = strval($worksheetSettlement->getCell('AL' . $i)->getValue()); //订单ID
                        } elseif ($region == 2 || $region == 3) {
                            $orderIdS = strval($worksheetSettlement->getCell('E' . $i)->getValue()); //订单ID
                        } else {
                            continue;
                        }
                        if ($worksheetSettlement->getCell('D' . $i)->getValue() != 'Order' || $worksheetSettlement->getCell('D' . $i)->getValue() != 'order') {
                            continue;
                        }*/

            //var_dump($orderIndexByOrderId);
            if (isset($orderIndexByOrderId[$orderIdS])) {
                $orderIndexByOrderId[$orderIdS]['totalSettlementAmount'] = $worksheetSettlement->getCell('J' . $i)->getValue(); //总结算金额
                //var_dump( $orderIndexByOrderId[$orderIdS]['totalSettlementAmount']);die();
                $orderIndexByOrderId[$orderIdS]['sellerShippingFee'] = $worksheetSettlement->getCell('U' . $i)->getValue(); //卖家实际支付运费
                $orderIndexByOrderId[$orderIdS]['transactionFee'] = $worksheetSettlement->getCell('S' . $i)->getValue(); //平台交易手续费
                $orderIndexByOrderId[$orderIdS]['tkCommissionFee'] = $worksheetSettlement->getCell('T' . $i)->getValue(); //平台佣金
                $orderIndexByOrderId[$orderIdS]['SPFServiceFee'] = $worksheetSettlement->getCell('AL' . $i)->getValue(); //SFP服务费
                $orderIndexByOrderId[$orderIdS]['SSTFee'] = $worksheetSettlement->getCell('AM' . $i)->getValue(); //增值税(SST)
                $orderIndexByOrderId[$orderIdS]['BonusCashbackServiceFee'] = $worksheetSettlement->getCell('AJ' . $i)->getValue(); //奖金返现服务费
                $orderIndexByOrderId[$orderIdS]['VoucherXtraServiceFee'] = $worksheetSettlement->getCell('AH' . $i)->getValue(); //超级优惠券服务费
                $orderIndexByOrderId[$orderIdS]['AffiliateCommission'] = $worksheetSettlement->getCell('AB' . $i)->getValue(); //联盟佣金
                /*                $orderIndexByOrderId[$orderIdS]['totalSettlementAmount'] = $totalSettlementAmount;//总结算金额
                                $orderIndexByOrderId[$orderIdS]['sellerShippingFee'] = $sellerShippingFee;//卖家实际支付运费
                                $orderIndexByOrderId[$orderIdS]['transactionFee'] = $transactionFee;//平台交易手续费
                                $orderIndexByOrderId[$orderIdS]['tkCommissionFee'] = $tkCommissionFee;*/
            }
        }


        //加载最终数据表格模板
        $orderSts = ROOT_PATH . 'public/template/settlementlist2511.xlsx';
        $orderStsSheet = IOFactory::load($orderSts);
        $worksheetWrite = $orderStsSheet->getActiveSheet();
        $from = 1;

        foreach ($orderIndexByOrderId as $key => $datum) {
            $from += 1;
            $worksheetWrite->getCell('A' . $from)->setValue($shopFlag);
            $worksheetWrite->getCell('B' . $from)->setValue("'" . $datum['orderId']);
            $worksheetWrite->getCell('C' . $from)->setValue($datum['skuId']);
            $worksheetWrite->getCell('G' . $from)->setValue($datum['quantity']);
            $worksheetWrite->getCell('E' . $from)->setValue($datum['orderStatus']);
            $worksheetWrite->getCell('F' . $from)->setValue($datum['sellPrice']);
            $worksheetWrite->getCell('H' . $from)->setValue($datum['orderAmount']);
            $worksheetWrite->getCell('I' . $from)->setValue($datum['createAt']);
            $worksheetWrite->getCell('J' . $from)->setValue($datum['warehouseName']);
            $worksheetWrite->getCell('K' . $from)->setValue($datum['cancelledAt']);
            $worksheetWrite->getCell('L' . $from)->setValue($datum['cancelReason']);
            $worksheetWrite->getCell('M' . $from)->setValue($datum['totalSettlementAmount']);
            $worksheetWrite->getCell('N' . $from)->setValue($datum['sellerShippingFee']);
            $worksheetWrite->getCell('O' . $from)->setValue($datum['transactionFee']);
            $worksheetWrite->getCell('P' . $from)->setValue($datum['tkCommissionFee']);
            $worksheetWrite->getCell('Q' . $from)->setValue($datum['SPFServiceFee']);
            $worksheetWrite->getCell('R' . $from)->setValue($datum['SSTFee']);
            $worksheetWrite->getCell('S' . $from)->setValue($datum['BonusCashbackServiceFee']);
            $worksheetWrite->getCell('T' . $from)->setValue($datum['VoucherXtraServiceFee']);
            $worksheetWrite->getCell('U' . $from)->setValue($datum['AffiliateCommission']);
            $goodsSku = $goodsSkuIndexBySkuNo[$datum['skuId']];
            $worksheetWrite->getCell('V' . $from)->setValue($goodsSku['cost']);//产品成本
            if ($datum['warehouseName'] == 'china') {
                $extra_fee = $goodsSku['export_extra_fee'];

            } else {
                //海外仓操作费
                $temp = bcadd(bcmul(strval(intval($datum['quantity']) - 1), $goodsSku['oversea_order_xujian_fee'], 2), $goodsSku['oversea_order_pack_rate'], 2);
                //加头程运费
                $extra_fee = bcadd($temp, $goodsSku['oversea_order_export_rate'], 2);
            }
            $worksheetWrite->getCell('W' . $from)->setValue($extra_fee);//操作杂费
            $orderAmountCNY = bcdiv($datum['totalSettlementAmount'], $exchangeRateVn, 2);
            $temp1 = bcsub($orderAmountCNY, $goodsSku['cost'], 2);
            $profit = bcsub($temp1, $extra_fee, 2);
            $worksheetWrite->getCell('X' . $from)->setValue($profit);//利润
        }

        $writer = IOFactory::createWriter($orderStsSheet, 'Xlsx');
        $filename = uniqid() . '.xlsx';
        $file_path = ROOT_PATH . 'public/uploads/' . $filename;
        //$writer->save($filename);
        $writer->save($file_path);

        $rs = Request::instance();
        $url = $rs->domain();


        $this->success('成功,请点击弹窗中的链接下载', '', ['link' => $url . '/uploads/' . $filename]);
    }


}
